package forumSystem;


import java.security.Policy;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
import java.util.Vector;



public class ForumRealDB {

	private Connection connection;

	public static String[] dbTables={"forums", "more", "policy","post","reply","subforummoderators","subforums","users"};

	public void Connect()
	{
		try
		{
			Class.forName("com.mysql.jdbc.Driver");
			connection = null;

			connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/forum", "root", "");
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	public String insertCommand(String TableName ,Vector<String> v)
	{
		String ans="INSERT INTO "+TableName+" VALUES (";
		for (int i = 0; i < v.size(); i++) {
			if(i+1<v.size())
			{
				ans+="'"+v.get(i)+"',";
			}
			else
			{
				ans+="'"+v.get(i)+"'";
			}
		}
		ans+=")";
		return ans;
	}

	public void truncate(Vector<String> names) throws SQLException
	{
		Connect();
		Statement statement;
		int resultSet;
		String sql;
		for (int i = 0; i < names.size(); i++) {
			statement = connection.createStatement();
			sql ="TRUNCATE `"+names.get(i)+"`;";
			resultSet = statement.executeUpdate(sql);
			if(statement!=null)
			{
				statement.close();
			}
			if(connection!=null)
			{
				connection.close();
			}
		}
	}

	public void initialize(String adminUsername, String pass) 
	{
		try
		{
			Connect();
			Statement statement;
			String sql;
			int resultSet;


			for (int i = 0; i < ForumRealDB.dbTables.length; i++) 
			{				
				statement = connection.createStatement();
				sql="DELETE FROM `"+ForumRealDB.dbTables[i]+"`;";
				resultSet = statement.executeUpdate(sql);
			}


			int year = Calendar.getInstance().get(Calendar.YEAR);
			int month = Calendar.getInstance().get(Calendar.MONTH);
			int day = Calendar.getInstance().get(Calendar.DAY_OF_MONTH);

			statement = connection.createStatement();
			Vector<String> v=new Vector<String>();
			v.add(adminUsername);
			v.add(pass);
			sql = insertCommand("more", v);
			System.out.println(sql);
			resultSet = statement.executeUpdate(sql);
			sql="INSERT INTO `users` (`rank` ,`userName` ,`pass` ,`ForumID`, `year` ,`month` ,`day`, `mail`) VALUES ('3',  '"+adminUsername+"',  '"+pass+"',  '-1',  '"+year+"',  '"+month+"',  '"+day+"',  'tallevi12@gmail.com')";
			resultSet = statement.executeUpdate(sql);
			if(statement!=null)
			{
				statement.close();
			}
			if(connection!=null)
			{
				connection.close();
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}

	public String getAdminUserName() 
	{
		String ans="";
		try
		{
			Connect();
			Statement statement = connection.createStatement();
			String sql = "SELECT * FROM more"; 
			ResultSet resultSet = statement.executeQuery(sql); 
			if (resultSet.next()) {
				ans=resultSet.getString("superAdminUserName");
			}

			if(statement!=null)
			{
				statement.close();
			}
			if(connection!=null)
			{
				connection.close();
			}

		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return ans;
	}

	public String getAdminPass() 
	{
		String ans="";
		try
		{
			Connect();
			Statement statement = connection.createStatement();
			String sql = "SELECT * FROM more"; 
			ResultSet resultSet = statement.executeQuery(sql); 
			if (resultSet.next()) {
				ans=resultSet.getString("superAdminPass");
			}

			if(statement!=null)
			{
				statement.close();
			}

			if(connection!=null)
			{
				connection.close();
			}

		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return ans;
	}

	public int addForum(String name, String desc) 
	{
		int forumid=-1;
		if(name != null)
		{
			try
			{
				Connect();

				String sql="INSERT INTO `Forums` (`adminID` ,`name` ,`desc`) VALUES ('1',  '"+name+"',  '"+desc+"');";
				Statement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				int affectedRows = statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
				ResultSet generatedKeys = statement.getGeneratedKeys();
				if (generatedKeys.next()) {
					Long l=generatedKeys.getLong(1);
					forumid=(int)l.longValue();
				} 
				if(statement!=null)
				{
					statement.close();
				}

				if(connection!=null)
				{
					connection.close();
				}

			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		return forumid;
	}

	public int addSubForum(String name, String desc, int parent, int moderatorId) 
	{
		int flag=-1;
		if(name != null && desc != null && parent>0 && moderatorId>0)
		{	
			try
			{
				Connect();

				String sql="INSERT INTO `subForums` (`parentForum` ,`title` ,`desc`) VALUES ('"+parent+"',  '"+name+"',  '"+desc+"');";
				System.out.println(sql);
				Statement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				int affectedRows = statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
				ResultSet generatedKeys = statement.getGeneratedKeys();
				if (generatedKeys.next()) {
					Long l=generatedKeys.getLong(1);
					flag=(int)l.longValue();
				} 
				sql="INSERT INTO `subForumModerators` (`subforumID` ,`moderatorID`) VALUES ('"+flag+"',  '"+moderatorId+"');";
				statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				affectedRows = statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
				generatedKeys = statement.getGeneratedKeys();
				statement = connection.createStatement();
				statement.executeUpdate( "UPDATE users SET rank = 1  WHERE id =" + moderatorId) ;

				if(statement!=null)
				{
					statement.close();
				}

				if(connection!=null)
				{
					connection.close();
				}

			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		return flag;
	}
	public int addManagerToForum(int forumID, int userid) 
	{
		int flag=-1;
		if(forumID > 0 && userid >0)
		{
			try
			{
				Connect();

				String sql="UPDATE  `Forums` SET  `adminID` =  '"+userid+"' WHERE  `Forums`.`id` ="+forumID+";";
				Statement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				int affectedRows = statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
				ResultSet generatedKeys = statement.getGeneratedKeys();
				if (generatedKeys.next()) {
					Long l=generatedKeys.getLong(1);
				} 
				if(statement!=null)
				{
					statement.close();
				}

				if(connection!=null)
				{
					connection.close();
				}

			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
			return forumID;

		}
		return flag;
	}
	public int addForumWithManager(String name, String desc, int userID, int policyID) 
	{
		int forumid=-1;
		if(name != null)
		{
			try
			{
				Connect();

				String sql="INSERT INTO `forums` (`adminID` ,`name` ,`desc` ,`policyID`) VALUES ('"+userID+"',  '"+name+"',  '"+desc+"',  '"+policyID+"');";
				Statement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				int affectedRows = statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
				ResultSet generatedKeys = statement.getGeneratedKeys();
				if (generatedKeys.next()) {
					Long l=generatedKeys.getLong(1);
					forumid=(int)l.longValue();
				} 
				statement = connection.createStatement();
				statement.executeUpdate( "UPDATE users SET rank = 3  WHERE id =" + userID) ;

				if(statement!=null)
				{
					statement.close();
				}

				if(connection!=null)
				{
					connection.close();
				}

			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		return forumid;
	}
	public int addModerateToSubForum(int subForumID, int moderateID) 
	{
		int flag=-1;
		if(subForumID > 0 && moderateID >0)
		{
			try
			{
				Connect();

				String sql="INSERT INTO `subForumModerators` (`subforumID` ,`moderatorID`) VALUES ('"+subForumID+"',  '"+moderateID+"');";
				Statement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				int affectedRows = statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
				ResultSet generatedKeys = statement.getGeneratedKeys();
				if(statement!=null)
				{
					statement.close();
				}

				if(connection!=null)
				{
					connection.close();
				}

			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
			return subForumID;

		}
		return flag;
	}

	public Vector<Forum> getForumlist() 
	{
		Vector<Forum> ans=new Vector<Forum>();
		try
		{
			Connect();
			Forum f;
			Statement statement = connection.createStatement();
			String sql = "SELECT * FROM forums"; 
			ResultSet resultSet = statement.executeQuery(sql); 
			while
				(resultSet.next()) 
			{
				f=new Forum(resultSet.getInt("id"), resultSet.getInt("policyID"), resultSet.getString("name"), resultSet.getString("desc"), resultSet.getInt("adminID"));
				ans.add(f);
			}
			if(statement!=null)
			{
				statement.close();
			}

			if(connection!=null)
			{
				connection.close();
			}

		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return ans;
	}

	public Vector<Integer> getSubForumModerators(int subForum) 
	{
		Vector<Integer> ans=new Vector<Integer>();
		try
		{
			Connect();
			Integer f;
			Statement statement = connection.createStatement();
			String sql = "SELECT moderatorID FROM subForumModerators WHERE subforumID="+subForum; 
			System.out.println(sql);
			ResultSet resultSet = statement.executeQuery(sql); 
			while
				(resultSet.next()) 
			{
				f=new Integer(resultSet.getInt("moderatorID"));
				ans.add(f);
			}
			if(statement!=null)
			{
				statement.close();
			}

			if(connection!=null)
			{
				connection.close();
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return ans;
	}

	public Vector<SubForum> getSubForumslist(int parent) 
	{
		Vector<SubForum> ans=new Vector<SubForum>();
		try
		{
			Connect();
			int id;
			SubForum f;
			Statement statement = connection.createStatement();
			String sql = "SELECT * FROM subForums WHERE parentForum="+parent; 
			ResultSet resultSet = statement.executeQuery(sql); 
			Vector<Integer> mod;
			while(resultSet.next()) 
			{
				id=resultSet.getInt("id");
				mod=getSubForumModerators(id);
				f=new SubForum(id, resultSet.getInt("parentForum"), resultSet.getString("title"), resultSet.getString("desc"),mod);
				ans.add(f);
			}
			if(statement!=null)
			{
				statement.close();
			}

			if(connection!=null)
			{
				connection.close();
			}

		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return ans;
	}
	public Forum getForumByID(int forumID) 
	{
		Forum f=null;
		try
		{
			Connect();
			int id;

			Statement statement = connection.createStatement();
			String sql = "SELECT * FROM Forums WHERE id="+forumID; 
			ResultSet resultSet = statement.executeQuery(sql); 
			while(resultSet!= null && resultSet.next()) 
			{
				f=new Forum(resultSet.getInt("id"),resultSet.getInt("policyID"), resultSet.getString("name"), resultSet.getString("desc"),resultSet.getInt("adminID"));
			}
			if(statement!=null)
			{
				statement.close();
			}

			if(connection!=null)
			{
				connection.close();
			}

		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return f;
	}
	public SubForum getSubForumByID(int subforumID) 
	{
		SubForum s=null;
		try
		{
			Connect();
			int id;

			Statement statement = connection.createStatement();
			String sql = "SELECT * FROM subForums WHERE id="+subforumID; 
			ResultSet resultSet = statement.executeQuery(sql); 
			while(resultSet.next()) 
			{
				id=resultSet.getInt("id");
				Vector<Integer> mod = getSubForumModerators(id);
				s=new SubForum(resultSet.getInt("id"), resultSet.getInt("parentForum"), resultSet.getString("title"), resultSet.getString("desc"), mod);
			}
			if(statement!=null)
			{
				statement.close();
			}

			if(connection!=null)
			{
				connection.close();
			}

		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return s;
	}
	public int addUser(String username, String pass, String mail, int forumId) 
	{
		int userid=-1;
		int year = Calendar.getInstance().get(Calendar.YEAR);
		int month = Calendar.getInstance().get(Calendar.MONTH);
		int day = Calendar.getInstance().get(Calendar.DAY_OF_MONTH);

		if(username != null && pass != null)
		{
			try
			{
				Connect();

				String sql="INSERT INTO `users` (`rank` ,`userName` ,`pass` ,`ForumID`, `year`, `month`, `day`, `mail`) VALUES ('1','"+username+"',  '"+pass+"',  '"+forumId+"',  '"+year+"',  '"+month+"',  '"+day+"',  '"+mail+"');";
				Statement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				int affectedRows = statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
				ResultSet generatedKeys = statement.getGeneratedKeys();
				if (generatedKeys.next()) {
					Long l=generatedKeys.getLong(1);
					userid=(int)l.longValue();
				} 
				if(statement!=null)
				{
					statement.close();
				}

				if(connection!=null)
				{
					connection.close();
				}

			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		return userid;
	}
	public User getUserByID(int id) 
	{
		User f=null;
		try
		{
			Connect();
			Statement statement = connection.createStatement();
			String sql = "SELECT * FROM users WHERE id="+id; 
			ResultSet resultSet = statement.executeQuery(sql); 
			while(resultSet.next()) 
			{
				f=new User(resultSet.getInt("id"), Rank.get(resultSet.getInt("rank")), resultSet.getString("userName"), resultSet.getString("pass"), resultSet.getInt("ForumID"), resultSet.getString("mail"), resultSet.getInt("year"), resultSet.getInt("month"), resultSet.getInt("day"));
			}
			if(statement!=null)
			{
				statement.close();
			}

			if(connection!=null)
			{
				connection.close();
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return f;
	}

	public int addPost(int userID, int subforumID, String title, String desc) 
	{
		int postID=-1;
		if(title != null && userID>0 && subforumID>0)
		{
			try
			{
				Connect();

				String sql="INSERT INTO `Post` (`title` ,`desc` ,`userID`,`subForumID`) VALUES ('"+title+"',  '"+desc+"',  '"+userID+"', '"+subforumID+"');";
				Statement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				int affectedRows = statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
				ResultSet generatedKeys = statement.getGeneratedKeys();
				if (generatedKeys.next()) {
					Long l=generatedKeys.getLong(1);
					postID=(int)l.longValue();
				} 
				if(statement!=null)
				{
					statement.close();
				}

				if(connection!=null)
				{
					connection.close();
				}

			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		return postID;
	}
	public Vector<Post> getSubForumPosts(int subforumID)
	{
		Vector<Post> ans=new Vector<Post>();
		try
		{
			Connect();
			int id;
			Post f;
			Statement statement = connection.createStatement();
			String sql = "SELECT * FROM Post WHERE subForumID="+subforumID; 
			ResultSet resultSet = statement.executeQuery(sql); 
			while(resultSet.next()) 
			{
				f =new Post(resultSet.getInt("id"), resultSet.getString("title"), resultSet.getString("desc"), resultSet.getInt("userID"), resultSet.getInt("subForumID"));
				ans.add(f);
			}
			if(statement!=null)
			{
				statement.close();
			}

			if(connection!=null)
			{
				connection.close();
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return ans;
	}

	public int addReply(int userID, int postID, String content) 
	{
		int repID=-1;
		if(content != null && userID>0 && postID>0)
		{
			try
			{
				Connect();

				String sql="INSERT INTO `Reply` (`content` ,`userID`,`postID`) VALUES ('"+content+"',  '"+userID+"',  '"+postID+"');";
				Statement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				int affectedRows = statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
				ResultSet generatedKeys = statement.getGeneratedKeys();
				if (generatedKeys.next()) {
					Long l=generatedKeys.getLong(1);
					repID=(int)l.longValue();
				} 
				if(statement!=null)
				{
					statement.close();
				}

				if(connection!=null)
				{
					connection.close();
				}

			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		return repID;
	}



	public Vector<Reply> getPostReplies(int postID)
	{
		Vector<Reply> ans=new Vector<Reply>();
		try
		{
			Connect();
			int id;
			Reply r;
			Statement statement = connection.createStatement();
			String sql = "SELECT * FROM Reply WHERE postID="+postID; 
			ResultSet resultSet = statement.executeQuery(sql); 
			while(resultSet.next()) 
			{
				r =new Reply(resultSet.getInt("id"), resultSet.getString("content"), resultSet.getInt("userID"), resultSet.getInt("postID"));
				ans.add(r);
			}
			if(statement!=null)
			{
				statement.close();
			}

			if(connection!=null)
			{
				connection.close();
			}

		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return ans;
	}

	public boolean userExist(String username)
	{
		boolean ans=false;
		if(username!=null)
		{
			try
			{
				Connect();
				Statement statement = connection.createStatement();
				String sql="SELECT * FROM users WHERE userName='"+username+"';";
				ResultSet resultSet = statement.executeQuery(sql); 
				if (resultSet.next()) {
					ans=true;
				}
				if(statement!=null)
				{
					statement.close();
				}

				if(connection!=null)
				{
					connection.close();
				}

			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		return ans;
	}

	public boolean forumExists(String name, int id)
	{
		boolean ans=false;
		if(name!=null)
		{
			try
			{
				Connect();
				Statement statement = connection.createStatement();
				String sql="SELECT * FROM Forums WHERE id="+id+" AND name='"+name+"';";
				ResultSet resultSet = statement.executeQuery(sql); 
				if (resultSet.next()) {
					ans=true;
				}
				if(statement!=null)
				{
					statement.close();
				}

				if(connection!=null)
				{
					connection.close();
				}

			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		return ans;
	}

	private boolean subForumExists(String name, int id)
	{
		boolean ans=false;
		if(name!=null)
		{
			try
			{
				Connect();
				Statement statement = connection.createStatement();
				String sql="SELECT * FROM subForums WHERE id="+id+" AND title='"+name+"';";
				ResultSet resultSet = statement.executeQuery(sql); 
				if (resultSet.next()) {
					ans=true;
				}
				if(statement!=null)
				{
					statement.close();
				}

				if(connection!=null)
				{
					connection.close();
				}

			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		return ans;
	}

	public boolean isUserExist(String username, String pass, int forumID) {
		boolean ans=false;
		if(username!=null && pass!=null)
		{
			try
			{
				Connect();
				Statement statement = connection.createStatement();
				String sql="SELECT * FROM users WHERE ForumID="+forumID+" AND userName='"+username+"' AND pass='"+pass+"';";
				ResultSet resultSet = statement.executeQuery(sql); 
				if (resultSet.next()) {
					ans=true;
				}
				if(statement!=null)
				{
					statement.close();
				}

				if(connection!=null)
				{
					connection.close();
				}

			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		return ans;
	}

	public Vector<Post> getForumPosts(int forumID) {
		Vector<SubForum> subForums = getSubForumslist(forumID);
		Vector<Post> result = new Vector<Post>();
		for (int i = 0; i < subForums.size(); i++) {
			result.addAll(getSubForumPosts(subForums.elementAt(i).getId()));
		}
		return result;
	}


	public User getUserByUserName(String username) {
		User ans=null;
		try
		{
			Connect();
			int id;
			Statement statement = connection.createStatement();
			String sql = "SELECT * FROM users WHERE userName='"+username+"';"; 
			ResultSet resultSet = statement.executeQuery(sql); 
			while(resultSet.next()) 
			{
				ans=new User(resultSet.getInt("id"), Rank.get(resultSet.getInt("rank")), resultSet.getString("userName"), resultSet.getString("pass"), resultSet.getInt("ForumID"), resultSet.getString("mail"), resultSet.getInt("year"), resultSet.getInt("month"), resultSet.getInt("day"));
			}
			if(statement!=null)
			{
				statement.close();
			}

			if(connection!=null)
			{
				connection.close();
			}

		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return ans;
	}

	public Vector<User> getUsers() {
		Vector<User> ans=new Vector<User>();
		try
		{
			Connect();
			User f;
			Statement statement = connection.createStatement();
			String sql = "SELECT * FROM users"; 
			ResultSet resultSet = statement.executeQuery(sql); 
			while
				(resultSet.next()) 
			{
				f=new User(resultSet.getInt("id"), Rank.get(resultSet.getInt("rank")), resultSet.getString("userName"), resultSet.getString("pass"), resultSet.getInt("ForumID"), resultSet.getString("mail"), resultSet.getInt("year"), resultSet.getInt("month"), resultSet.getInt("day"));
				ans.add(f);
			}
			if(statement!=null)
			{
				statement.close();
			}

			if(connection!=null)
			{
				connection.close();
			}

		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return ans;
	}

	public Post getPostByID(int id) {
		Post ans=null;
		try
		{
			Connect();
			Statement statement = connection.createStatement();
			String sql = "SELECT * FROM Post WHERE id="+id; 
			ResultSet resultSet = statement.executeQuery(sql); 
			while(resultSet.next()) 
			{
				ans=new Post(resultSet.getInt("id"), resultSet.getString("title"), resultSet.getString("desc"), resultSet.getInt("userID"), resultSet.getInt("subForumID"));
			}
			if(statement!=null)
			{
				statement.close();
			}

			if(connection!=null)
			{
				connection.close();
			}

		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return ans;
	}

	public int getUserRank(int i) 
	{
		User u = getUserByID(i);
		if(u == null) return -1;
		return u.getRank().ordinal();
	}

	public int getSubForumIdByTopicId(int TopicId) 
	{
		Post t = this.getPostByID(TopicId);
		if(t == null) return -1;
		return t.getSubForumID();
	}

	public int getForumIdByTopicId(int TopicId) 
	{
		Post t = this.getPostByID(TopicId);
		if(t == null) return -1;
		SubForum sf = this.getSubForumByID(this.getSubForumIdByTopicId(TopicId));
		if(sf == null) return -1;

		return sf.getParentForum();
	}

	public int isModerator(int userId, int subforumID) 
	{
		SubForum sf = this.getSubForumByID(subforumID);
		if(sf == null) return -1;

		return sf.isModeratorId(userId) ? 1 : 0;
	}


	public int removeTopic(int topicId) 
	{
		int ans = -1;
		try
		{
			Connect();
			Statement statement = connection.createStatement();
			String sql = "DELETE FROM Post WHERE id =" + topicId; 
			ans = statement.executeUpdate(sql); 
			if(statement!=null)
			{
				statement.close();
			}

			if(connection!=null)
			{
				connection.close();
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		if(ans==0) // if we didnt remove anything , return -1
		{
			ans=-1;
		}

		return ans;
	}

	public int getParentForumID(int subforumID) 
	{
		SubForum sf = this.getSubForumByID(subforumID);
		if(sf == null) return -1;

		return sf.getParentForum();
	}

	public int removeSubForum(int subForumId) 
	{
		int ans = -1;
		try
		{
			Connect();
			Statement statement = connection.createStatement();
			String sql = "DELETE FROM subForums WHERE id =" + subForumId; 
			ans = statement.executeUpdate(sql);
			if(statement!=null)
			{
				statement.close();
			}

			if(connection!=null)
			{
				connection.close();
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		if(ans==0) // if we didnt remove anything , return -1
		{
			ans=-1;
		}
		return ans;
	}

	public int removeModeretor(int user, int subforum) 
	{
		int ans = -1;
		try
		{
			Connect();
			Statement statement = connection.createStatement();
			String sql = "DELETE FROM subforummoderators WHERE subforumID =" + subforum+" AND  	moderatorID ="+user; 
			ans = statement.executeUpdate(sql);
			if(statement!=null)
			{
				statement.close();
			}

			if(connection!=null)
			{
				connection.close();
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		if(ans==0) // if we didnt remove anything , return -1
		{
			ans=-1;
		}
		return ans;
	}

	public Vector<Post> getUserPosts(int userId) 
	{
		Vector<Post> ans=new Vector<Post>();
		try
		{
			Connect();
			int id;
			Post f;
			Statement statement = connection.createStatement();
			String sql = "SELECT * FROM Post WHERE userID="+userId; 
			ResultSet resultSet = statement.executeQuery(sql); 
			while(resultSet.next()) 
			{
				f =new Post(resultSet.getInt("id"), resultSet.getString("title"), resultSet.getString("desc"), resultSet.getInt("userID"), resultSet.getInt("subForumID"));
				ans.add(f);
			}
			if(statement!=null)
			{
				statement.close();
			}

			if(connection!=null)
			{
				connection.close();
			}

		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return ans;
	}

	public Vector<String> getCommonUsernames() 
	{
		Vector<String> ans = new Vector<String>();
		return ans;
	}

	public Vector<User> getusersReplyToUserID(int userId) 
	{
		return null;
	}

	public boolean isUserExist2(String username, String pass) {
		boolean ans = false;
		try
		{
			Connect();
			Statement statement = connection.createStatement();
			String sql = "SELECT * FROM users WHERE userName ='" + username + 
					"' AND pass ='" + pass+"';"; 
			ResultSet resultSet = statement.executeQuery(sql); 
			if (resultSet.next())
				ans = true;
			if(statement!=null)
			{
				statement.close();
			}

			if(connection!=null)
			{
				connection.close();
			}

		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return ans;
	}

	public int addAdmin(int userID, int forumID) {
		int ans = -1;
		User user = getUserByID(userID);
		if(user != null && userExist(user.getUsername()))
		{
			try
			{
				Connect();
				Statement statement = connection.createStatement();
				ans = statement.executeUpdate( "UPDATE users SET rank = 2  WHERE id =" + userID) ;

				statement = connection.createStatement();
				ans = statement.executeUpdate( "UPDATE Forums SET adminID =" + userID + " WHERE id =" + forumID) ;

				if(statement!=null)
				{
					statement.close();
				}

				if(connection!=null)
				{
					connection.close();
				}
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		if (ans == 0)
			return -1;
		return ans;
	}

	public Reply getReplyByID(int replyID) {
		Reply ans = null;
		try
		{
			Connect();
			Statement statement = connection.createStatement();
			String sql = "SELECT * FROM Reply WHERE id =" + replyID; 
			ResultSet resultSet = statement.executeQuery(sql); 
			if (resultSet.next())
				ans = new Reply(resultSet.getInt("id"), resultSet.getString("content"), 
						resultSet.getInt("userID"), resultSet.getInt("postID"));
			if(statement!=null)
			{
				statement.close();
			}

			if(connection!=null)
			{
				connection.close();
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return ans;
	}


	public int addPolicy(int whenNotify, int whoNotify, int howOften,
			int deletePostPremission, int addModeratorPermission,
			int removeModeratorPermission, int securityPolicy,
			int badWordsPolicy) 
	{
		int policyID=-1;

		try
		{
			Connect();

			String sql="INSERT INTO `policy` (`whenNotify` ,`whoNotify` ,`howOften` ,`deletePostPermission` ,`addModeratorPermission` ,`removeModeratorPermission` ,`badWordsPolicy` ,`securityPolicy`) VALUES ('"+whenNotify+"',  '"+whoNotify+"',  '"+howOften+"',  '"+deletePostPremission+"',  '"+addModeratorPermission+"',  '"+removeModeratorPermission+"',  '"+badWordsPolicy+"',  '"+securityPolicy+"');";
			Statement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			int affectedRows = statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
			ResultSet generatedKeys = statement.getGeneratedKeys();
			if (generatedKeys.next()) {
				Long l=generatedKeys.getLong(1);
				policyID=(int)l.longValue();
			} 
			if(statement!=null)
			{
				statement.close();
			}

			if(connection!=null)
			{
				connection.close();
			}

		}
		catch(Exception e)
		{
			e.printStackTrace();
		}

		return policyID;
	}
	public ForumPolicy getPolicyByID(int PolicyID) 
	{
		ForumPolicy f=null;
		try
		{
			Connect();
			int id;

			Statement statement = connection.createStatement();
			String sql = "SELECT * FROM policy WHERE id="+PolicyID; 
			ResultSet resultSet = statement.executeQuery(sql); 
			while(resultSet.next()) 
			{
				f=new ForumPolicy(resultSet.getInt("whenNotify"), resultSet.getInt("whoNotify"), resultSet.getInt("howOften"), resultSet.getInt("deletePostPermission"), resultSet.getInt("addModeratorPermission"), resultSet.getInt("removeModeratorPermission"), resultSet.getInt("securityPolicy") ,resultSet.getInt("badWordsPolicy") );
			}
			if(statement!=null)
			{
				statement.close();
			}

			if(connection!=null)
			{
				connection.close();
			}

		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return f;
	}
	public int removeForum(int forumID) {
		int ans = -1;
		try
		{
			Connect();
			Statement statement = connection.createStatement();
			String sql = "DELETE FROM forums WHERE id =" + forumID; 
			ans = statement.executeUpdate(sql);
			if(statement!=null)
			{
				statement.close();
			}

			if(connection!=null)
			{
				connection.close();
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		if(ans==0) // if we didnt remove anything , return -1
		{
			ans=-1;
		}
		return ans;
	}
	public int removeReply(int replyID) {
		int ans = -1;
		try
		{
			Connect();
			Statement statement = connection.createStatement();
			String sql = "DELETE FROM reply WHERE id =" + replyID; 
			ans = statement.executeUpdate(sql);
			if(statement!=null)
			{
				statement.close();
			}

			if(connection!=null)
			{
				connection.close();
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		if(ans==0) // if we didnt remove anything , return -1
		{
			ans=-1;
		}
		return ans;		
	}

	public String getSubForumLastPost(int subforumid)
	{
		String ans=null;
		int userid=0;
		try
		{
			Connect();
			Statement statement = connection.createStatement();
			String sql="SELECT * FROM post WHERE subForumID='"+subforumid+"' ORDER BY id DESC;";
			ResultSet resultSet = statement.executeQuery(sql); 
			if (resultSet.next()) {
				userid=resultSet.getInt("userID");
			}
			if(statement!=null)
			{
				statement.close();
			}

			if(connection!=null)
			{
				connection.close();
			}

		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		if (userid == 0)
			return "no one";
		
		User user = getUserByID(userid);
		if (user == null)
			return "no one";
		return user.getUsername();
	}


	public void editPost(int postID, String content) {

		try
		{
			Connect();

			Statement statement = connection.createStatement();

			statement.executeUpdate("UPDATE `post` SET  `desc` = '"+content+"' WHERE `id`="+postID+";") ;

			if(statement!=null)
			{
				statement.close();
			}

			if(connection!=null)
			{
				connection.close();
			}

		}
		catch(Exception e)
		{
			e.printStackTrace();
		}

	}

	public void editReply(int replyID, String content) {

		try
		{
			Connect();

			Statement statement = connection.createStatement();

			statement.executeUpdate("UPDATE `reply` SET  `content` = '"+content+"' WHERE `id`="+replyID+";") ;

			if(statement!=null)
			{
				statement.close();
			}

			if(connection!=null)
			{
				connection.close();
			}

		}
		catch(Exception e)
		{
			e.printStackTrace();
		}

	}


}


